[MyBatis] mybatis

动态sql

if标签

根据查询条件来动态拼装sql语句，其格式如下：

1	<if test=""></if>

其中test表示判断表达式（使用OGNL），其从参数中取值进行判断，当然遇见特殊符号需要些转义字符，比如&&，其在xml文件中无法识别，需要使用转义字符。看下面这个例子：

<select id="getEmpsByConditionIf" resultType="com.glemontree.mybatis.bean.Employee">
    select * from tbl_employee where 
    <!-- 
         test：判断表达式 (OGNL)
         从参数中取值进行判断
         遇见特殊符号应该去写转义字符：比如&&
    -->
    <if test="id != null">
      	id = #{id}
    </if>
    <if test="lastName != null and lastName != ''">
      	and last_name like #{lastName} 
    </if>
    <if test="email != null and email.trim() != ''">
      	and email = #{email}
    </if>
    <!-- ognl会进行字符串与数字自动转换 -->
    <if test="gender == 0 or gender == 1">
      	and gender = #{gender}
    </if>
</select>

此时会根据传入参数的情况动态拼接sql语句，下面看下测试用例：

@Test
public void testDynamicSql() throws IOException {
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
        Employee employee = new Employee(2, "%e%", "jerry@gmail.com", null);
        List<Employee> employees = mapper.getEmpsByConditionIf(employee);
        for (Employee emp : employees) {
          	System.out.println(emp);
        }
    } finally {
      	openSession.close();
    }
}

在上面的sql语句拼接中如果id为null的话，sql语句拼接会出现问题，有以下两种解决方法：

给where后面加上1=1，以后的条件都是and xxx，如下：

<select id="getEmpsByConditionIf" resultType="com.glemontree.mybatis.bean.Employee">
    select * from tbl_employee where 1 = 1 
    <!-- 
         test：判断表达式 (OGNL)
         从参数中取值进行判断
         遇见特殊符号应该去写转义字符：比如&&
    -->
    <if test="id != null">
      	and id = #{id}
    </if>
    <if test="lastName != null and lastName != ''">
      	and last_name like #{lastName} 
    </if>
    <if test="email != null and email.trim() != ''">
      	and email = #{email}
    </if>
    <!-- ognl会进行字符串与数字自动转换 -->
    <if test="gender == 0 or gender == 1">
      	and gender = #{gender}
    </if>
</select>

mybatis使用where标签来将所有的查询条件包括在内，mybatis就会将where标签中拼装的sql多出来的and或者or去掉，where只会去掉第一个多出来的and或者or：

<select id="getEmpsByConditionIf" resultType="com.glemontree.mybatis.bean.Employee">
    select * from tbl_employee
  	<where>
        <!-- 
             test：判断表达式 (OGNL)
             从参数中取值进行判断
             遇见特殊符号应该去写转义字符：比如&&
        -->
        <if test="id != null">
            id = #{id}
        </if>
        <if test="lastName != null and lastName != ''">
            and last_name like #{lastName} 
        </if>
        <if test="email != null and email.trim() != ''">
            and email = #{email}
        </if>
        <!-- ognl会进行字符串与数字自动转换 -->
        <if test="gender == 0 or gender == 1">
            and gender = #{gender}
        </if>
    </where>
</select>

trim标签

看下面这个例子：

<select id="getEmpsByConditionTrim" resultType="com.glemontree.mybatis.bean.Employee">
	select * from tbl_employee 
  	<!-- 
        后面多出的and 或者 or where标签不能解决
        prefix：前缀 trim标签体中时整个字符串拼串后的结果，prefix给拼串后的整个字符串加一个前缀
        prefixOverrides：前缀覆盖，去掉整个字符串前面多余的字符
        suffix：后缀 给拼串后的整个字符串加一个后缀
        suffixOverrides：后缀覆盖，去掉整个字符串后面多余的字符
   -->
    <trim prefix="where" prefixOverrides="" suffix="" suffixOverrides="and" >
        <if test="id != null">
          	id = #{id} and
        </if>
        <if test="lastName != null and lastName != ''">
          	last_name like #{lastName} and
        </if>
        <if test="email != null and email.trim() != ''">
          	email = #{email} and
        </if>
        <!-- ognl会进行字符串与数字自动转换 -->
        <if test="gender == 0 or gender == 1">
          	gender = #{gender}
        </if>
    </trim>
</select>

在这种情况下，将and添加在每个if标签中语句的后面，此时如果gender为空的话，email语句后面会多出一个and，那么在执行查询的时候就会报错，此时可使用trim标签。

choose标签

其起到分支选择的作用，类型与switch-case。

<select id="getEmpsByConditionChoose" resultType="com.glemontree.mybatis.bean.Employee">
  	select * from tbl_employee
    <where>
        <choose>
            <when test="id != null">
              	id = #{id}
            </when>
            <when test="lastName != null">
              	last_name like #{lastName}
            </when>
            <when test="email != null">
              	email = #{email}
            </when>
            <otherwise>
              	gender = 0
            </otherwise>
        </choose>
    </where>
</select>

set标签

<update id="updateEmp">
    update tbl_employee
    <set>
        <if test="lastName != null">
          	last_name = #{lastName},
        </if>
        <if test="email != null">
          	email = #{email},
        </if>
        <if test="gender != null">
          	gender = #{gender}
        </if>
    </set>
    <where>
      	id = #{id}
    </where> 
</update>

加上set标签后可以去掉多余的,。

foreach标签

foreach用作批量查询

<select id="getEmpsByConditionForeach" resultType="com.glemontree.mybatis.bean.Employee">
    select * from tbl_employee where id in
    <!-- 
        collection：指定要遍历的集合，list类型的参数会特殊处理封装在map中，map的key就叫list
        item：遍历出的元素赋给指定的变量
        #{变量名}就能取出变量值，也就是当前遍历出的元素
        open：遍历出所有结果拼接一个开始的字符
        close：遍历出所有结果拼接一个结束的字符
        index：遍历list时是索引，item就是当前值，遍历map时表示key，item就是map的值
    -->
  	<!--
		separator是放在foreach中每个条目之间的
	-->
    <foreach collection="ids" item="item_id" separator="," open="(" close=")">
      	#{item_id}
    </foreach>
</select>

测试代码如下：

List<Employee> list = mapper.getEmpsByConditionForeach(Arrays.asList(1, 2, 3, 4));
for (Employee emp : list) {
  	System.out.println(emp);
}

foreach用作批量保存

<!--MySQL下批量保存，可以foreach遍历，mysql支持values(),(),()语法-->
<insert id="addEmps">
    insert into tbl_employee(last_name, email, gender)
    values
    <foreach collection="emps" item="emp" separator=",">
      	(#{emp.lastName}, #{emp.email}, #{emp.gender})
    </foreach>
</insert>

同样的，这里的separator逗号是放在(#{emp.lastName}, #{emp.email}, #{emp.gender})条目之间的。其测试代码如下：

@Test
public void testBatchSave() throws IOException {
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    SqlSession openSession = sqlSessionFactory.openSession();
    try {
        EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
        List<Employee> emps = new ArrayList<Employee>();
        emps.add(new Employee(null, "smith", "smith@gmail.com", "1"));
        emps.add(new Employee(null, "allen", "allen@gmail.com", "0"));
        mapper.addEmps(emps);
        openSession.commit();
    } finally {
      	openSession.close();
    }
}

需要注意的，在执行增删改时不要忘记调用openSession.commit();。

这里还有另外一种写法：

<insert id="addEmps">
    <foreach collection="emps" item="emp" separator=";">
      	insert into tbl_employee(last_name, email, gender)
    values
      	(#{emp.lastName}, #{emp.email}, #{emp.gender})
    </foreach>
</insert>

这样写之后是让MySQL一次执行多条sql语句，需要让MySQL支持一次执行多条sql语句，在MySQL的配置中加入：

1	jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true

这种使用分号（;）分隔多个sql可以用于其他的批量操作（删除、修改）。

oracle数据库批量保存

oracle不支持使用values(),(),()，oracle下可以使用下面的语句插入一条记录：

1	insert into employees(employee_id, last_name, email) values(employee_seq.nextval, 'test_01', 'test_01@gmail.com');

employee_seq.nextval用于获取下一个id号。

但是oracle不支持values(),(),()，在oracle下可以使用下面的这些批量方式：

多个insert放在begin - end之间

begin
	insert into employees(employee_id, last_name, email) 	values(employee_seq.nextval, 'test_01', 'test_01@gmail.com');
	insert into employees(employee_id, last_name, email) values(employee_seq.nextval, 'test_02', 'test_02@gmail.com');
end;

利用中间表

insert into employees(employee_id, last_name, email)
	select employees_seq.nextval, lastName, email form (
    	select 'test_a_01' lastName, 'test_a_e01' email from dual
      	union
      	select 'test_a_02' lastName, 'test_a_e02' email from dual
      	union
      	select 'test_a_03' lastName, 'test_a_e03' email from dual
    )

下面看下在mybatis中的实现：

<!--第一种批量方式-->
<insert id="addEmps" databaseId="oracle">
  	begin
  		<foreach collection="emps" item="emp">
          	insert into employees(employee_id, last_name, email) 
          	values(employee_seq.nextval, #{emp.lastName}, #{emp.email});
  		</foreach> 
  	end;
</insert>
<!--第二种批量方式-->
<insert id="addEmps" databaseId="oracle">
    insert into employees(employee_id, last_name, email)
        select employees_seq.nextval, lastName, email form (
            <foreach collection="emps" item="emp" separator="union">
              select #{emp.lastName} lastName, #{emp.email} email from dual
            </foreach>
        )
</insert>

两个内置参数

两个内置参数分别为_parameter和_databaseId。

_parameter代表整个参数，如果是单个参数，_parameter就表示这个参数，如果是多个参数，参数会被封装为一个map，_parameter就是代表这个map。

_databaseId如果配置了DatabaseIdProvider标签，_databaseId就代表当前数据库的别名。

<select id="getEmpsTestInnerParameter" resultType="com.glemontree.mybatis.bean.Employee">
  	<if test="_databaseId=='mysql'">
  		select * from tbl_employee
      	<!--无法直接通过传入的employee参数判断是否有参数，因为如果你通过#{lastName}!=null来判断，你已经默认了employee不为空，所以只能通过_parameter!=null来判断-->
      	<if test="_parameter!=null">
      		where
          	last_name=#{lastName}
      	</if>
  	</if>
  	<if test="_databaseId=='oracle'">
  		select * from employees
  	</if>
</select>

bind标签

bind标签可以将OGNL表达式的值绑定到一个变量中方便后来引用这个变量的值：
1
<bind name="_lastName" value="'%' + lastName + '%'"/>
这样在代码中传入参数后可以通过bind标签修改这个参数绑定到一个新的变量中以便在后面引用。
1
2
3
4
select * from tbl_employee
<if test="_parameter != null">
where last_name like #{_lastName}
</if>
sql标签（与增删改查标签同级的标签）

sql标签用来抽取可重用的sql片段，方便后面引用：
1
2
3
<sql id="insertColumn">
employee_id, last_name, email
</sql>
然后就可以在后面进行引用，原来我们是这样写的：
1
insert into employees(employee_id, last_name, email)
现在我们可以这样写：
1
2
3
4
insert into employees(

<include refid="insertColumn"/>
)
同样的，sql标签里面也可以写动态标签，比如：
1
2
3
4
5
6
7
8
<sql id="insertColumn">
<if test="_databaseId=='oracle'">
employee_id, last_name, email
</if>
<if test="_databaseId=='mysql'">
last_name, email, gender
</if>
</sql>
在实际的编程中：
- sql抽取：经常将要查询的列名或者插入用的列名抽取出来方便引用。
- include来引用已经抽取的sql
- include还可以自定义一些property，sql标签内部就能使用自定义的属性：${prop}

[MyBatis] mybatis

谢谢打赏